Getting started with SQLite

SQLite is a relational database engine. In this guide, we will show how you can spawn a process with SQLite and work with data using a relational database.

Setup

NOTE: make sure you have aos installed, if not checkout Welcome & Quick Start

spawn a new process mydb with a --sqlite flag, this instructs ao to use the latest sqlite module.

aos mydb --sqlite

Install AO Package Manager

installing apm, the ao package manager we can add helper modules to make it easier to work with sqlite.

.load-blueprint apm

Install dbAdmin package

DbAdmin is a module that connects to a sqlite database and provides functions to work with sqlite.

https://apm_betteridea.g8way.io/pkg?id=@rakis/DbAdmin

apm.install('@rakis/dbAdmin')

Create sqlite Database

local sqlite = require('lsqlite3')
Db = sqlite.open_memory()
dbAdmin = require('@rakis/DbAdmin').new(Db)

Create Table

Create a table called Comments

dbAdmin:exec([[
  CREATE TABLE IF NOT EXISTS Comments (
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    Asset TEXT,
    User TEXT,
    Body TEXT
  );
]])

Insert data

local SQL = "INSERT INTO Comments (Asset, User, Body) VALUES (?,?,?);"
dbAdmin:apply(SQL, {"dog.jpg", "Anon", "Nice Picture"})

List data

local SQL = "SELECT * FROM Comments;"
dbAdmin:exec(SQL)

Congrats!

You are using sqlite on AO 🎉